﻿USE KBManagement
GO
CREATE TRIGGER store_UPDATE
ON Store AFTER UPDATE AS
BEGIN
	DECLARE @unit NVARCHAR(20)
	DECLARE @name NVARCHAR(50)
	DECLARE @StrQuantity NVARCHAR(20)
	DECLARE @Quantity INT
	DECLARE @QuantityStandard INT
	SELECT @unit = Unit FROM inserted
	SELECT @Quantity = Quantity FROM inserted
	SELECT @QuantityStandard = QuantityStandard FROM inserted
	SELECT @name = S_Name FROM inserted
	IF @QuantityStandard > @Quantity
	BEGIN
	 SET @StrQuantity = @QuantityStandard - @Quantity
		UPDATE Store SET warning = N'Xin mời bạn nhập thêm ' + @StrQuantity+' '+@unit+ ' '+ @name+' vào kho' WHERE ID IN (SELECT ID FROM inserted)
	END
	ELSE
	BEGIN
	UPDATE Store SET warning = N'Kho hàng ổn định' WHERE ID IN (SELECT ID FROM inserted)
	END
END

GO


--CREATE TRIGGER OnMonthSalaryInsert ON MonthSalary FOR INSERT
--AS
--BEGIN
--      DECLARE @MsId INT;
--	  SET @MsId = (SELECT MsId FROM inserted);
--	  DECLARE tempEmpId CURSOR FOR SELECT ID FROM Employee
--		  OPEN tempEmpId
--		  DECLARE @EmpId AS INT
--		  FETCH NEXT FROM tempEmpId INTO @EmpId
--		  WHILE @@FETCH_STATUS = 0
--			  BEGIN
--			  INSERT INTo Pay_Salary values(@EmpId,@MsId,'','','','','0')
--			  FETCH NEXT FROM tempEmpId INTO @EmpId
--			  END
--		  CLOSE tempEmpId
--	  DEALLOCATE tempEmpId
--END
--GO
--CREATE TRIGGER Pay_Salary_insert
--ON Pay_Salary AFTER INSERT AS
--BEGIN

--	DECLARE @salary MONEY
--	DECLARE @penalty MONEY
--	DECLARE @reward MONEY
--	DECLARE @tip MONEY
--	DECLARE @count INT
--	SET @penalty = 0
--	SET @reward = 0
--	SET @tip = 0
--	SET @count = 0
--	SELECT @salary = Salary FROM Employee WHERE ID IN (SELECT EmployeeID FROM inserted)
--	SELECT @count = COUNT(p.ExceptSalary) FROM Penalty_Detail pd,Penalty p WHERE pd.PenaltyID = p.ID AND pd.EmployeeID IN (SELECT EmployeeID FROM inserted)
--	IF @count>0
--	BEGIN
--	SELECT @penalty = SUM(p.ExceptSalary) FROM Penalty_Detail pd,Penalty p WHERE pd.PenaltyID = p.ID AND pd.EmployeeID IN (SELECT EmployeeID FROM inserted)
--	END
--	SET @count = 0
--	SELECT @count = count(Amount) FROM Reward WHERE EmployeeID IN (SELECT EmployeeID FROM inserted)
--	IF @count>0
--	BEGIN
--	SELECT @reward = SUM(Amount) FROM Reward WHERE EmployeeID IN (SELECT EmployeeID FROM inserted)
--	END
--	SET @count = 0
--	SELECT @count = count(Tip_Money_Receive) FROM Tip WHERE EmployeeId IN (SELECT EmployeeID FROM inserted)
--	IF @count>0
--	BEGIN
--	SELECT @tip = SUM(Tip_Money_Receive) FROM Tip WHERE EmployeeId IN (SELECT EmployeeID FROM inserted)
--	END
--	UPDATE Pay_Salary SET TotalPenaltyMoney = @penalty,TotalRewardMoney = @reward,TotalTipMoney = @tip,TotalAmount = @salary - @penalty + @reward + @tip WHERE PsId IN (SELECT PsId FROM inserted)
--END

GO
--CREATE TRIGGER MonthSalary_UPDATE
--ON MonthSalary AFTER UPDATE AS
--BEGIN
--	DECLARE @MsId INT;
--	  SET @MsId = (SELECT MsId FROM inserted);
--	  DECLARE tempEmpId CURSOR FOR SELECT EmployeeID FROM Pay_Salary WHERE MsId = @MsId
--		  OPEN tempEmpId
--		  DECLARE @EmpId AS INT
--		  FETCH NEXT FROM tempEmpId INTO @EmpId
--		  WHILE @@FETCH_STATUS = 0
--			  BEGIN
--				DECLARE @salary MONEY
--				DECLARE @penalty MONEY
--				DECLARE @reward MONEY
--				DECLARE @tip MONEY
--				DECLARE @count INT
--				SET @penalty = 0
--				SET @reward = 0
--				SET @tip = 0
--				SET @count = 0
--				SELECT @salary = Salary FROM Employee WHERE ID = @EmpId
--				SELECT @count = count(p.ExceptSalary) FROM Penalty_Detail pd,Penalty p WHERE pd.PenaltyID = p.ID AND pd.EmployeeID = @EmpId
--				IF @count>0
--				BEGIN
--				SELECT @penalty = SUM(p.ExceptSalary) FROM Penalty_Detail pd,Penalty p WHERE pd.PenaltyID = p.ID AND pd.EmployeeID = @EmpId
--				END
--				SET @count = 0
--				SELECT @count = count(Amount) FROM Reward WHERE EmployeeID = @EmpId
--				IF @count>0
--				BEGIN
--				SELECT @reward = SUM(Amount) FROM Reward WHERE EmployeeID = @EmpId
--				END
--				SET @count = 0
--				SELECT @count = count(Tip_Money_Receive) FROM Tip WHERE EmployeeId = @EmpId
--				IF @count>0
--				BEGIN
--				SELECT @tip = SUM(Tip_Money_Receive) FROM Tip WHERE EmployeeId = @EmpId
--				END
--				UPDATE Pay_Salary SET TotalPenaltyMoney = @penalty,TotalRewardMoney = @reward,TotalTipMoney = @tip,TotalAmount = @salary - @penalty + @reward + @tip WHERE MsId = @MsId AND EmployeeID = @EmpId
--			  FETCH NEXT FROM tempEmpId INTO @EmpId
--			  END
--		  CLOSE tempEmpId
--	  DEALLOCATE tempEmpId
--END


GO

CREATE TRIGGER Penalty_Detail_insert
ON Penalty_Detail AFTER INSERT AS
BEGIN
	DECLARE @idPenalty INT
	DECLARE @amount MONEY
	SELECT @idPenalty = PenaltyID FROM inserted
	SELECT @amount = ExceptSalary FROM Penalty WHERE ID = @idPenalty
	UPDATE  funds_general SET balance = balance + @amount
END


GO

CREATE TRIGGER tip_insert
ON Tip AFTER INSERT AS
BEGIN
DECLARE @money MONEY
DECLARE @tipMoney MONEY
DECLARE @percent float
	SELECT @percent = Tip_Percent_Receive FROM Percent_Tip
	SELECT @tipMoney = Tip_Money FROM Tip WHERE Tip_Id IN (SELECT Tip_Id FROM inserted)
	UPDATE Tip SET Tip_Money_Receive = Tip_Money * @percent/100 ,Tip_Balance_Money = Tip_Money - Tip_Money * @percent/100 WHERE Tip_Id IN (SELECT Tip_Id FROM inserted) 
	SELECT @money = Tip_Balance_Money FROM Tip WHERE Tip_Id IN (SELECT Tip_Id FROM inserted)
	UPDATE Pay_Salary set TotalTipMoney = TotalTipMoney + @tipMoney * @percent/100;
	UPDATE funds_general SET balance = balance + @money
END

GO

CREATE TRIGGER tip_update
ON Tip AFTER UPDATE AS
BEGIN
DECLARE @money MONEY
DECLARE @percent FLOAT
	SELECT @percent = Tip_Percent_Receive FROM Percent_Tip
	UPDATE Tip SET Tip_Money_Receive = Tip_Money * @percent/100 ,Tip_Balance_Money = Tip_Money - Tip_Money * @percent/100 WHERE Tip_Id IN (SELECT Tip_Id FROM inserted) 
	SELECT @money = Tip_Balance_Money FROM Tip WHERE Tip_Id IN (SELECT Tip_Id FROM inserted)
	UPDATE funds_general SET balance = balance + @money
END


GO
--drop TRIGGER StroreTemp_update
go
CREATE TRIGGER StroreTemp_update
ON StoreTemp AFTER UPDATE AS
BEGIN
DECLARE @quantity MONEY
	SELECT @quantity = Quantity FROM StoreTemp WHERE ID IN (SELECT ID FROM inserted)
	IF @quantity<=0
	BEGIN
		UPDATE StoreTemp SET Warning = N'Đã hết hàng hãy xuất từ kho ra' WHERE ID IN (SELECT ID FROM inserted) 
	END
	ELSE
	BEGIN
		UPDATE StoreTemp SET Warning = '' WHERE ID IN (SELECT ID FROM inserted) 
	END
END

GO

CREATE TRIGGER reward_insert
ON Reward AFTER INSERT AS
BEGIN
	DECLARE @amount MONEY
	SELECT @amount = Amount FROM inserted
	UPDATE  funds_general SET balance = balance - @amount
END
